﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class SalaryPromoteDal
    {
        private Common common = new Common();

        private DBManager dbManager = new DBManager(DataProvider.MySQL,
                                                    ConfigurationSettings.AppSettings["ConnectionString"]);

        public List<salary_promote> Select (salary_promote filter = null)
        {
            var results = SelectHead(filter);
            var details = SelectDetail(null);

            var employeDal = new Employee();
            var employees = employeDal.GetHrmPsEmployeeLite();
            var positions = employeDal.GetHrmMpPositions(null, null, null, null, null, null, null, null, false, false,
                                                         false, false, false, false);
            if(results != null)
                foreach (salary_promote t1 in results)
                {
                    var detail = details.Where(o => o.sp_seq == t1.sp_seq).ToList();
                    foreach (salary_promote_detail t in detail)
                    {
                        t.Position = positions.FirstOrDefault(o => o.pos_seq == t.pos_seq);
                        t.Employee = employees.FirstOrDefault(o => o.emp_seq == t.emp_seq);
                    }
                    t1.Details = detail;
                }
            return results;
        }
        public List<salary_promote> SelectHead(salary_promote filter = null)
        {
            var result = new List<salary_promote>();
            if(filter == null) filter = new salary_promote();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                int i = 0;
                dbManager.AddParameters(i++, "@deletedStatus", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@spSeq", filter.sp_seq); 

                string strSql = @"
SELECT  `sp_seq`,   `sp_cmd_title`,   `effected_date`,    
  `remarks`,   `record_status`,   `created_by`,
  `created_when`,  `updated_by`,  `updated_when`
FROM `hrm_mp_salary_promote`
WHERE record_status != @deletedStatus ";

                if (!common.IsIntNullOrZero(filter.sp_seq))
                {
                    strSql += " and sp_seq = @spSeq ";
                }

                DataRowCollection drc = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0].Rows;
                foreach (DataRow dr in drc)
                {
                    result.Add(new salary_promote
                        {
                            sp_seq = Convert.ToInt32(dr["sp_seq"]),
                            sp_cmd_title = Convert.ToString(dr["sp_cmd_title"]),
                            effected_date = common.ConvertToNullableDateTime(dr["effected_date"]), 
                            remarks = Convert.ToString(dr["remarks"]),
                            record_status = Convert.ToString(dr["record_status"]),
                            created_by = common.ConvertToNullableInt(dr["created_by"]),
                            created_when = common.ConvertToNullableDateTime(dr["created_when"]),
                            updated_by = common.ConvertToNullableInt(dr["updated_by"]),
                            updated_when = common.ConvertToNullableDateTime(dr["updated_when"])
                        });
                } 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

             
            return result;
        }
        public List<salary_promote_detail> SelectDetail(int? sp_seq = null)
        {
            var result = new List<salary_promote_detail>(); 

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                int i = 0;
                dbManager.AddParameters(i++, "@sp_seq", sp_seq);

                string strSql = @"SELECT
sp_seq,
  `spd_seq`,
  `pos_seq`,
  `emp_seq`,
  `old_salary`,
  `new_salary`
FROM `hrm_mp_salary_promote_detail`
" + (sp_seq.HasValue ? " where sp_seq = @sp_seq " : "");
                 
                DataRowCollection drc = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0].Rows;
                foreach (DataRow dr in drc)
                {
                    result.Add(new salary_promote_detail
                    {
                        spd_seq = Convert.ToInt32(dr["spd_seq"]),
                        sp_seq = Convert.ToInt32(dr["sp_seq"]),
                        pos_seq = Convert.ToInt32(dr["pos_seq"]),
                        emp_seq = Convert.ToInt32(dr["emp_seq"]),
                        old_salary = Convert.ToDecimal(dr["old_salary"]),
                        new_salary = Convert.ToDecimal(dr["new_salary"])
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }


            return result;
        } 

        public int Insert(salary_promote sp)
        {
            int seq;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                int i = 0;
                dbManager.AddParameters(i++, "@waitStatus", Common.WaitStatus);
                dbManager.AddParameters(i++, "@sp_cmd_title", sp.sp_cmd_title);
                dbManager.AddParameters(i++, "@effected_date", sp.effected_date);
                dbManager.AddParameters(i++, "@remarks", sp.remarks);
                dbManager.AddParameters(i++, "@created_by", sp.created_by);

                string strSql = @"
 INSERT INTO `hrm_mp_salary_promote`
            ( `sp_cmd_title`,
             `effected_date`, 
             `remarks`,
             `record_status`,
             `created_by`,
             `created_when`,
             `updated_by`,
             `updated_when`)
VALUES (@sp_cmd_title,
        @effected_date, 
        @remarks,
        @waitStatus,
        @created_by,
        now(),
        @created_by,
        now());";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
                strSql = @"select last_insert_id() from hrm_mp_salary_promote";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

                seq = Convert.ToInt32(dtb.Rows[0][0]);
                 
                dbManager.CreateParameters(6);
                foreach (var detail in sp.Details)
                {
                    i = 0;
                    dbManager.AddParameters(i++, "@spd_seq", detail.spd_seq);
                    dbManager.AddParameters(i++, "@sp_seq", seq);
                    dbManager.AddParameters(i++, "@pos_seq", detail.pos_seq);
                    dbManager.AddParameters(i++, "@emp_seq", detail.emp_seq);
                    dbManager.AddParameters(i++, "@old_salary", detail.old_salary);
                    dbManager.AddParameters(i++, "@new_salary", detail.new_salary);
                    string strSql2 = @" INSERT INTO `hrm_mp_salary_promote_detail`
            (`spd_seq`,
             `sp_seq`,
             `pos_seq`,
             `emp_seq`,
             `old_salary`,
             `new_salary`)
VALUES (@spd_seq,
        @sp_seq,
        @pos_seq,
        @emp_seq,
        @old_salary,
        @new_salary);";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSql2);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            return seq;
        }

        public void Update(salary_promote sp)
        {
            UpdateHead(sp);
            UpdateDetail(sp);
        }
        
        public void UpdateHead(salary_promote sp)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                int i = 0; 
                dbManager.AddParameters(i++, "@sp_cmd_title", sp.sp_cmd_title);
                dbManager.AddParameters(i++, "@effected_date", sp.effected_date);
                dbManager.AddParameters(i++, "@remarks", sp.remarks);
                dbManager.AddParameters(i++, "@updated_by", sp.updated_by);
                dbManager.AddParameters(i++, "@record_status", sp.record_status); 

                string strSql = @"
UPDATE `hrm_mp_salary_promote`
SET  `sp_cmd_title` = @sp_cmd_title,
  `effected_date` = @effected_date, 
  `remarks` = @remarks,
  `record_status` = @record_status,
  `updated_by` = @updated_by,
  `updated_when` = NOW()
WHERE `sp_seq` = @sp_seq;";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
                 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }
        public void UpdateDetail(salary_promote sp)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                int i = 0;
                dbManager.AddParameters(i++, "@sp_seq", sp.sp_seq); 
                string strSql = @"delete from hrm_mp_salary_promote where sp_seq = @sp_seq"; 
                dbManager.ExecuteNonQuery(CommandType.Text, strSql);

                dbManager.CreateParameters(6);
                foreach (var detail in sp.Details)
                {
                    i = 0;
                    dbManager.AddParameters(i++, "@spd_seq", detail.spd_seq);
                    dbManager.AddParameters(i++, "@sp_seq", detail.sp_seq);
                    dbManager.AddParameters(i++, "@pos_seq", detail.pos_seq);
                    dbManager.AddParameters(i++, "@emp_seq", detail.emp_seq);
                    dbManager.AddParameters(i++, "@old_salary", detail.old_salary);
                    dbManager.AddParameters(i++, "@new_salary", detail.new_salary);
                    string strSql2 = @" INSERT INTO `hrm_mp_salary_promote_detail`
            (`spd_seq`,
             `sp_seq`,
             `pos_seq`,
             `emp_seq`,
             `old_salary`,
             `new_salary`)
VALUES (@spd_seq,
        @sp_seq,
        @pos_seq,
        @emp_seq,
        @old_salary,
        @new_salary);";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSql2);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void Delete(int sp_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                int i = 0;
                dbManager.AddParameters(i++, "@deletedStatus", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@sp_seq", sp_seq);
                dbManager.AddParameters(i++, "@updated_by", updated_by);

                string strSql = @"
UPDATE `hrm_mp_salary_promote`
SET record_status = @deletedStatus,
    updated_by = @updated_by,
    updated_when = now()
WHERE `sp_seq` = @sp_seq;

UPDATE `hrm_mp_salary_promote_detail`
SET record_status = @deletedStatus
    updated_by = @updated_by,
    updated_when = now()
WHERE `sp_seq` = @sp_seq;";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

        }
    }
}